--
-- Incorporacion de cadena de tipos de comprobantes a precios especiales
--

ALTER TABLE PREESP DROP COLUMN IF EXISTS TiposCom;
ALTER TABLE PREESP ADD COLUMN TiposCom character varying;
COMMENT ON COLUMN preesp.TiposCom IS 'Tipos de comprobantes habilitados para este precio especial';

-- 
DROP FUNCTION IF EXISTS pre_obtenerprecioctxt(character varying, integer, character varying, character varying, character varying, character varying, character varying, integer, character varying, character varying, character varying, character varying, character varying, double precision, character varying, integer);

CREATE OR REPLACE FUNCTION pre_obtenerprecioctxt(
    character varying,
    integer,
    character varying,
    character varying,
    character varying,
    character varying,
    character varying,
    integer,
    character varying,
    character varying,
    character varying,
    character varying,
    character varying,
    double precision,
    character varying,
    integer,
    character varying
    )
  RETURNS numeric AS
$BODY$
DECLARE
vble 		ALIAS FOR $1;
id_ART 		ALIAS FOR $2;
fechaEntrada 	ALIAS FOR $3;
--*contexto: viene del comprobante*--
Ctxt_TIPOPREPAGA ALIAS FOR $4;
Ctxt_CODPREPAG   ALIAS FOR $5;
Ctxt_TIPOPADA    ALIAS FOR $6;
Ctxt_CODPADR     ALIAS FOR $7;
Ctxt_IDPAD       ALIAS FOR $8;
Ctxt_TIPOARTA    ALIAS FOR $9;
Ctxt_CODARTR     ALIAS FOR $10;
Ctxt_FECHAINI    ALIAS FOR $11; 
Ctxt_FECHAFIN    ALIAS FOR $12; 
Ctxt_CODSUC      ALIAS FOR $13;
--valor para la vble de precio  especial  fija #CANT
cantNUMERAL_CANT ALIAS FOR $14;	
codAmbTur        ALIAS FOR $15;
NroCierre        ALIAS FOR $16;
Ctxt_TipoCom     ALIAS FOR $17;	

--*-------------------------------*--
fechaConsulta	VARCHAR;
precioArticulo	NUMERIC;
vCantNUMERAL_CANT NUMERIC;
valorNUMERAL_CANT VARCHAR;
DummyInfo       VARCHAR;
BEGIN
IF (COALESCE(fechaEntrada, '') = '') OR (fechaEntrada = '') THEN
fechaConsulta := CAST(CURRENT_DATE AS VARCHAR);
ELSE
fechaConsulta := fechaEntrada;
END IF;
vCantNUMERAL_CANT := CAST(cantNUMERAL_CANT AS NUMERIC);	
IF (COALESCE(cantNUMERAL_CANT, 0) = 0) OR (ROUND(vCantNUMERAL_CANT, 4) = 0.0000) THEN
valorNUMERAL_CANT := '0.0000';
ELSE
valorNUMERAL_CANT := CAST(ROUND(vCantNUMERAL_CANT, 4) AS VARCHAR);
END IF;
SELECT * 
FROM pre_ObtenerPrecioArticuloInfo(vble, id_ART, fechaConsulta, Ctxt_TIPOPREPAGA, Ctxt_CODPREPAG,
Ctxt_TIPOPADA, Ctxt_CODPADR, Ctxt_IDPAD, 
Ctxt_TIPOARTA, Ctxt_CODARTR, 
Ctxt_FECHAINI, Ctxt_FECHAFIN, Ctxt_CODSUC, valorNUMERAL_CANT,
CodAmbTur, NroCierre, Ctxt_TipoCom)
INTO precioArticulo, DummyInfo;
RETURN precioArticulo;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION pre_obtenerprecioctxt(character varying, integer, character varying, character varying, character varying, character varying, character varying, integer, character varying, character varying, character varying, character varying, character varying, double precision, character varying, integer, character varying)
  OWNER TO abacouser;
COMMENT ON FUNCTION pre_obtenerprecioctxt(character varying, integer, character varying, character varying, character varying, character varying, character varying, integer, character varying, character varying, character varying, character varying, character varying, double precision, character varying, integer, character varying)
    IS 'Funcion llamada desde la app. (ART_BuscaRegistro__Data, PRE__Data)';




-- Function: pre_obtenerprecioarticuloinfo(character varying, integer, character varying, character varying, character varying, character varying, character varying, integer, character varying, character varying, character varying, character varying, character varying, character varying, character, integer)

DROP FUNCTION IF EXISTS pre_obtenerprecioarticuloinfo(character varying, integer, character varying, character varying, 
     character varying, character varying, character varying, integer, character varying, character varying, 
     character varying, character varying, character varying, character varying, character, integer);

CREATE OR REPLACE FUNCTION pre_obtenerprecioarticuloinfo(
    IN character varying,
    IN integer,
    IN character varying,
    IN character varying,
    IN character varying,
    IN character varying,
    IN character varying,
    IN integer,
    IN character varying,
    IN character varying,
    IN character varying,
    IN character varying,
    IN character varying,
    IN character varying,
    IN character,
    IN integer,
    IN character varying,
    OUT precio numeric,
    OUT infoprecio character varying)
  RETURNS SETOF record AS
$BODY$ 
DECLARE 
vble 		ALIAS FOR $1; 
id_ART 		ALIAS FOR $2; 
fechaEntrada 	ALIAS FOR $3; 
Ctxt_TIPOPREPAGA ALIAS FOR $4; 
Ctxt_CODPREPAG   ALIAS FOR $5; 
Ctxt_TIPOPADA    ALIAS FOR $6; 
Ctxt_CODPADR     ALIAS FOR $7; 
Ctxt_IDPAD       ALIAS FOR $8; 
Ctxt_TIPOARTA    ALIAS FOR $9; 
Ctxt_CODARTR     ALIAS FOR $10; 
Ctxt_FECHAINI    ALIAS FOR $11;  
Ctxt_FECHAFIN    ALIAS FOR $12;  
Ctxt_CODSUC      ALIAS FOR $13;	 
valorNUMERAL_CANT ALIAS FOR $14; 
codambtur        ALIAS FOR $15;  
nrocierre        ALIAS FOR $16;
Ctxt_TipoCom     ALIAS FOR $17;  
--=== parametros de salida === 
pout_precio      ALIAS FOR $18; 
pout_infoprecio  ALIAS FOR $19; 
--=== auxiliares === 
pre_decimales	INTEGER; 
pre_metodoRedondeo VARCHAR; 
formulaNumerica VARCHAR; 
pre_codprered VARCHAR; 
pre_valormonedamenor NUMERIC; 
precioArt	NUMERIC; 
infoPreXArt     VARCHAR; 
BEGIN 
SELECT digPrec, calcPrec,  codprered, valormonedamenor 
INTO pre_decimales, pre_metodoRedondeo,  pre_codprered, pre_valormonedamenor 
FROM pre WHERE (cod = vble); 
infoPreXArt := ''; 

-- Analizo tema IVA cero para consumidores finales y MONOTRIBUTISTAS
if (vble = 'IVA') AND
   ( (SELECT CODPADIVA FROM PAD WHERE ID = Ctxt_IDPAD) IN ('F', 'M') or 
     ( exists (select * from padc where tipopada = 'CIV' and codpadr = 'MP' and idpad = Ctxt_IDPAD) )  )  AND 
   EXISTS (SELECT * FROM ARTC WHERE TIPOARTA = 'IVA' AND CODARTR = 'IVA0' AND IDART = id_ART)
THEN
   formulaNumerica := '';
   infoPreXArt     := 0.00;
ELSE
IF (vble = 'IVAG') THEN
   formulaNumerica := pre_ObtenerPrecio(cast('IVA' as varchar),id_ART,cast(NOW() as varchar),Ctxt_CODSUC);
   infoPreXArt := pre_ObtenerPrecio(cast('IVA' as varchar),id_ART,cast(NOW() as varchar),Ctxt_CODSUC);
ELSE

SELECT * FROM pre_ObtenerFormulaInfo(vble, id_ART, 
fechaEntrada, Ctxt_TIPOPREPAGA, Ctxt_CODPREPAG, 
Ctxt_TIPOPADA, Ctxt_CODPADR, Ctxt_IDPAD, Ctxt_TIPOARTA, Ctxt_CODARTR, 
Ctxt_FECHAINI, Ctxt_FECHAFIN, Ctxt_CODSUC, 
CodAmbTur, NroCierre, infoPreXArt, Ctxt_TipoCom) 
INTO formulaNumerica, infoPreXArt;

  END IF;
END IF;
 
IF (COALESCE(formulaNumerica, '') <> '') THEN 
precioArt := pre_Eval(formulaNumerica, valorNUMERAL_CANT); 
ELSE 
precioArt := 0.0000;  -- (-999999999); 
END IF; 
/* 
Redondear el valor de precio calculado: 
1) segun pre.digprec 
2) segun precios sicologicos (si pre.codprered <> NULL) 
3) segun valor menor valido para la moneda (si pre.valormonedamenor <> NULL) 
*/ 
IF pre_metodoRedondeo = 'T' THEN 
precioArt := pre_TruncarValor(precioArt, pre_decimales); 
ELSIF pre_metodoRedondeo = 'RS' THEN  
precioArt := pre_RedondearSupValor(precioArt, pre_decimales); 
ELSIF pre_metodoRedondeo = 'R' THEN 
precioArt := pre_RedondearValor(precioArt, pre_decimales); 
ELSIF pre_metodoRedondeo = '99' THEN 
precioArt := pre_Redondear99Valor(precioArt, pre_decimales); 
ELSIF pre_metodoRedondeo = '50' THEN 
precioArt := pre_Redondear50Valor(precioArt, pre_decimales); 
END IF; 
-- ==== Si define precios sicologicos para la variable de precios (pre.codprered <> NULL) ==== 
IF pre_codprered IS NOT NULL THEN 
precioArt  := pre_ObtenerPrecioSicologico(precioArt, pre_codprered); 
END IF; 
-- ==== Si define valor menor de moneda (pre.valormonedamenor <> NULL) ==== 
IF pre_valormonedamenor IS NOT NULL THEN 
precioArt := pre_redondearvalormoneda(precioArt, pre_valormonedamenor, pre_metodoRedondeo); 
END IF; 
IF precioArt IS NULL THEN 
precioArt := 0.0000; 
END IF; 
SELECT CAST(precioArt AS NUMERIC (16, 4)), infoPreXArt INTO pout_precio, pout_infoprecio; 
RETURN NEXT; --agrega los datos al conjunto resultado de salida 
RETURN; 
END; 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION pre_obtenerprecioarticuloinfo(character varying, integer, character varying, character varying, character varying, character varying, character varying, integer, character varying, character varying, character varying, character varying, character varying, character varying, character, integer, character varying)
  OWNER TO abacouser;

-- Function: pre_obtenerformulainfo(character varying, integer, character varying, character varying, character varying, character varying, character varying, integer, character varying, character varying, character varying, character varying, character varying, character varying, integer, character)

-- 

DROP FUNCTION IF EXISTS pre_obtenerformulainfo(character varying, integer, character varying, character varying, character varying, character varying, character varying, integer, character varying, character varying, character varying, character varying, character varying, character varying, integer, character);

CREATE OR REPLACE FUNCTION pre_obtenerformulainfo(
    IN character varying,
    IN integer,
    IN character varying,
    IN character varying,
    IN character varying,
    IN character varying,
    IN character varying,
    IN integer,
    IN character varying,
    IN character varying,
    IN character varying,
    IN character varying,
    IN character varying,
    IN character varying,
    IN integer,
    IN character,
    IN character varying,
    OUT character varying,
    OUT character varying)
  RETURNS SETOF record AS
$BODY$

DECLARE

formulaEntrada	 ALIAS FOR $1;

id_ART	 	 ALIAS FOR $2;

fechaEntrada	 ALIAS FOR $3;

Ctxt_TIPOPREPAGA ALIAS FOR $4;

Ctxt_CODPREPAG   ALIAS FOR $5;

Ctxt_TIPOPADA    ALIAS FOR $6;

Ctxt_CODPADR     ALIAS FOR $7;

Ctxt_IDPAD       ALIAS FOR $8;

Ctxt_TIPOARTA    ALIAS FOR $9;

Ctxt_CODARTR     ALIAS FOR $10;

Ctxt_FECHAINI    ALIAS FOR $11;

Ctxt_FECHAFIN    ALIAS FOR $12;

Ctxt_CODSUC      ALIAS FOR $13;

CodAmbTur        ALIAS FOR $14;

NroCierre        ALIAS FOR $15;

InfoFormula      ALIAS FOR $16;

Ctxt_TipoCom     ALIAS FOR $17;

--=== parametros de salida

pout_Formula        ALIAS FOR $18;

pout_InfoFormula    ALIAS FOR $19;

formulaOriginal	VARCHAR;

formulaReemplazada  	VARCHAR;

formulaFinal  	VARCHAR;

infoFinal  	        VARCHAR;

separador		INTEGER;

valorVble		VARCHAR;

infoVble		VARCHAR;

vble			VARCHAR;

longitud 		INTEGER;

i 			INTEGER;

charSeparador		CHAR;

BEGIN

charSeparador := '-';

formulaOriginal := formulaEntrada;

--parsear formula

formulaReemplazada := pre_ParsearFormula(formulaOriginal, charSeparador);

IF (COALESCE(formulaReemplazada, '') = '') OR (formulaReemplazada = charSeparador) THEN

BEGIN

pout_Formula := formulaOriginal;

pout_InfoFormula := InfoFormula;

RETURN NEXT; --agrega los datos al conjunto resultado de salida

RETURN;

--============================

END;

END IF;

longitud := LENGTH(formulaReemplazada);

formulaFinal := formulaOriginal;

InfoFinal    := infoFormula;

--recorro en formulaReemplazada para recuperar las distintas vbles

i := 1;

WHILE i <= longitud LOOP

BEGIN

separador := POSITION(charSeparador in formulaReemplazada);

/*cuando (separador = longitud) la formula es del tipo VTA-*/

IF (separador > 0) and (separador < longitud) THEN

BEGIN

vble := SUBSTR(formulaReemplazada, 1 , separador-1);	

formulaReemplazada := SUBSTR(formulaReemplazada, separador + 1, longitud);

i := separador + 1;

END;			

ELSE BEGIN

vble := REPLACE(formulaReemplazada, '-', '');

i := longitud + 1;

END;

END IF;

IF vble <> '' THEN

BEGIN

SELECT * FROM pre_ObtenerValorInfo(vble, id_ART, 

fechaEntrada, Ctxt_TIPOPREPAGA, Ctxt_CODPREPAG,

Ctxt_TIPOPADA, Ctxt_CODPADR, Ctxt_IDPAD, 

Ctxt_TIPOARTA, Ctxt_CODARTR, 

Ctxt_FECHAINI, Ctxt_FECHAFIN, Ctxt_CODSUC, 

CodAmbTur, NroCierre, Ctxt_TipoCom)

INTO valorVble, infoVble;



formulaFinal := public.pre_reemplazar_valor_variable(formulaFinal, vble, valorVble);


if (COALESCE(InfoFinal, '') = '') AND (COALESCE(InfoVble, '') <>'') THEN

InfoFinal := infoVble;

END IF;

END;

END IF;

END;			

END LOOP;

-- retornar parametros de salida as record

pout_Formula := formulaFinal;

pout_InfoFormula := InfoFinal;

RETURN NEXT; --agrega los datos al conjunto resultado de salida

RETURN;

END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION pre_obtenerformulainfo(character varying, integer, character varying, character varying, character varying, character varying, character varying, integer, character varying, character varying, character varying, character varying, character varying, character varying, integer, character, character varying)
  OWNER TO abacouser;


-- Function: pre_obtenervalorinfo(character varying, integer, character varying, character varying, character varying, character varying, character varying, integer, character varying, character varying, character varying, character varying, character varying, character varying, integer)

DROP FUNCTION IF EXISTS pre_obtenervalorinfo(character varying, integer, character varying, character varying, character varying, character varying, character varying, integer, character varying, character varying, character varying, character varying, character varying, character varying, integer);

CREATE OR REPLACE FUNCTION pre_obtenervalorinfo(
    IN character varying,
    IN integer,
    IN character varying,
    IN character varying,
    IN character varying,
    IN character varying,
    IN character varying,
    IN integer,
    IN character varying,
    IN character varying,
    IN character varying,
    IN character varying,
    IN character varying,
    IN character varying,
    IN integer,
    IN character varying,
    OUT character varying,
    OUT character varying)
  RETURNS SETOF record AS
$BODY$
DECLARE
vble 		 ALIAS FOR $1;
id_ART		 ALIAS FOR $2;
fechaEntrada	 ALIAS FOR $3;
Ctxt_TIPOPREPAGA ALIAS FOR $4;
Ctxt_CODPREPAG   ALIAS FOR $5;
Ctxt_TIPOPADA    ALIAS FOR $6;
Ctxt_CODPADR     ALIAS FOR $7;
Ctxt_IDPAD       ALIAS FOR $8;
Ctxt_TIPOARTA    ALIAS FOR $9;
Ctxt_CODARTR     ALIAS FOR $10;
Ctxt_FECHAINI    ALIAS FOR $11;
Ctxt_FECHAFIN    ALIAS FOR $12;
Ctxt_CODSUC      ALIAS FOR $13;
codambtur        ALIAS FOR $14;
nrocierre        ALIAS FOR $15;
Ctxt_TipoCom     ALIAS FOR $16;
--=== parametros de salida
id_PRE		INTEGER;
prexartporfechaPRE VARCHAR;
escotizacionPRE VARCHAR;
formulaPRE VARCHAR;
codvalmonPRE VARCHAR;
pout_Formula      ALIAS FOR $17;
pout_InfoFormula  ALIAS FOR $18;
valorPRExART	VARCHAR;
infoPRExART     VARCHAR;
valorFormula	VARCHAR;
infoFormula     VARCHAR; 
valorRUBROSEC	VARCHAR;
valorRUBROPPAL	VARCHAR;
valorPRECIOESP	VARCHAR;
valorCTZ 	VARCHAR;
BEGIN
IF (vble IN ('#PULTV','#PULTC')) THEN
BEGIN
  valorFormula := pre_obtenerprecioultimacompraventa(vble, id_art, Ctxt_IDPAD, fechaentrada);
END;
ELSE
BEGIN
  --recupero datos de la variable de precio
  SELECT id, prexartporfecha, escotizacion, formula, codvalmon  FROM pre WHERE (cod = vble)
  INTO id_PRE, prexartporfechaPRE, escotizacionPRE, formulaPRE, codvalmonPRE;
  --recupero valor/formula de Precios Especiales
  valorPRECIOESP = pre_ObtenerPrecioEspecial(id_PRE, id_ART, Ctxt_TIPOPREPAGA, Ctxt_CODPREPAG, Ctxt_TIPOPADA, Ctxt_CODPADR, 
                                             Ctxt_IDPAD, Ctxt_TIPOARTA, Ctxt_CODARTR, Ctxt_FECHAINI, Ctxt_FECHAFIN, Ctxt_CODSUC, Ctxt_TipoCom);
  --si el articulo no tiene precio especial
  IF (valorPRECIOESP = '') OR (valorPRECIOESP IS NULL) THEN
  BEGIN
    --recupero el valor en prexart
    --================================================================
    -- SELECT COALESCE(CAST(valor AS VARCHAR), '') INTO valorPRExART FROM prexart WHERE (idart = id_ART) AND (idpre = id_PRE);
    IF prexartporfechaPRE = 'S' THEN
    BEGIN
      SELECT INTO valorPRExART, infoPRExART
      COALESCE(CAST(valor AS VARCHAR), ''), 
      COALESCE(to_char(fechaprecio, 'DD/MM/YYYY'), '') ||
      COALESCE('-'|| CAST(codambturprecio AS VARCHAR), '') || 
      COALESCE('('|| CAST(nrocierreprecio AS VARCHAR)||')', '')          
      FROM prexart 
      WHERE (idart = id_ART) AND (idpre = id_PRE)
      AND COALESCE(fechaprecio, '-INFINITY') <= CAST (COALESCE(fechaentrada, 'INFINITY') AS TIMESTAMP)
      AND COALESCE(codambturPrecio, '') <= COALESCE(codambtur, 'zzzzzzzzzz')
      AND COALESCE(NroCierrePrecio, 0)  <= COALESCE(nrocierre, 9999)
      ORDER BY fechaprecio DESC NULLS LAST, 
      codambturprecio DESC NULLS LAST,
      nrocierreprecio DESC NULLS LAST
      LIMIT 1;
    END;
    ELSE
      SELECT INTO valorPRExART, infoPRExART
      COALESCE(CAST(valor AS VARCHAR), ''), CAST('' AS VARCHAR)
      FROM prexart 
      WHERE (idart = id_ART) AND (idpre = id_PRE)
      AND fechaprecio IS NULL
      AND codambturPrecio IS NULL
      AND NroCierrePrecio  IS NULL;
    END IF;
    --==============================================================
    --si el articulo no esta en prexart
    IF ( COALESCE(valorPRExART, '') = '' ) THEN
    BEGIN
      --recupero valor/formula en prexrub del Arbol Secundario
      SELECT COALESCE(prexrubSEC.formula, '') INTO valorRUBROSEC FROM artc
      INNER JOIN prexrub prexrubSEC ON  artc.tipoarta = prexrubSEC.tipoarta AND  artc.codartr || '.' LIKE prexrubSEC.codartr || '.%'
      WHERE (artc.idart = id_ART) AND (idpre = id_PRE)
      ORDER BY pre_CantOcurrencias(prexrubSEC.codartr, '.') DESC, prexrubSEC.codartr DESC LIMIT 1;
      --si el articulo no esta en un arbol secundario
      IF (valorRUBROSEC = '') OR (valorRUBROSEC IS NULL) THEN
      BEGIN
      --recupero el valor/formula en prexrub del Arbol Principal
        SELECT COALESCE(prexrubPPAL.formula, '') INTO valorRUBROPPAL FROM art
        INNER JOIN prexrub prexrubPPAL ON art.tipoarta = prexrubPPAL.tipoarta  AND art.codartr || '.'  LIKE prexrubPPAL.codartr || '.%'
        WHERE (art.id = id_ART) AND (idpre = id_PRE)
        ORDER BY pre_CantOcurrencias(prexrubPPAL.codartr, '.') DESC,  prexrubPPAL.codartr DESC LIMIT 1;
      --si el articulo no esta en el arbol ppal con vbles propias
        IF (valorRUBROPPAL = '') OR (valorRUBROPPAL IS NULL) THEN
        BEGIN
          --recupero el valor/formula de pre o valctz segun sea o no cotizacion de moneda
          IF escotizacionPRE = 'S' THEN
          BEGIN
            SELECT CAST(valctz.cotizacion AS VARCHAR) INTO valorCTZ
            FROM valctz 
            WHERE valctz.codvalmon = codvalmonPRE AND ( CAST(DATE(valctz.vigencia) AS VARCHAR) <= fechaEntrada )
            ORDER BY valctz.vigencia DESC LIMIT 1;
            valorFormula := valorCTZ;
          END;
          ELSE
            valorFormula := formulaPRE;
          END IF;
        END;
        ELSE --el articulo esta en el arbol ppal
          valorFormula := valorRUBROPPAL;
        END IF;
      END;
      ELSE --el articulo esta en un arbol secundario
        valorFormula := valorRUBROSEC;
      END IF;
    END;
    ELSE --el articulo esta en prexart
      valorFormula := valorPRExART;
    infoFormula  := InfoPREXART;
    END IF;
  END;
  ELSE --el articulo tiene precio especial
    valorFormula := valorPRECIOESP;
  END IF;
END;
END IF;

if (vble = 'IVA') AND
   ( (SELECT CODPADIVA FROM PAD WHERE ID = Ctxt_IDPAD) IN ('F', 'M') or 
     ( exists (select * from padc where tipopada = 'CIV' and codpadr = 'MP' and idpad = Ctxt_IDPAD) )  )  AND 
   EXISTS (SELECT * FROM ARTC WHERE TIPOARTA = 'IVA' AND CODARTR = 'IVA0' AND IDART = id_ART)
THEN
   valorFormula := 0.00;
ELSE
  IF (vble = 'IVAG') THEN
     valorFormula := pre_ObtenerPrecio(cast('IVA' as varchar),id_ART,cast(NOW() as varchar),Ctxt_CODSUC);
  END IF;
END IF;

  
--devolver el valor/formula entre ()
valorFormula := '(' || valorFormula || ')';
--sacar enters, tabs y returns del valor/formula recuperado
valorFormula := REPLACE(valorFormula, chr(13)||chr(10), ''); 	--retorno de carro + nueva linea
valorFormula := REPLACE(valorFormula, chr(9),''); 		--tab
valorFormula := REPLACE(valorFormula, chr(10), ''); 		--nueva linea ( chr(10) = '' )
SELECT * FROM pre_ObtenerFormulaInfo(valorFormula, id_ART, fechaEntrada, Ctxt_TIPOPREPAGA, Ctxt_CODPREPAG, Ctxt_TIPOPADA, Ctxt_CODPADR, Ctxt_IDPAD, 
                                     Ctxt_TIPOARTA, Ctxt_CODARTR, Ctxt_FECHAINI, Ctxt_FECHAFIN, Ctxt_CODSUC, CodAmbTur, NroCierre, infoFormula, Ctxt_TipoCom)
INTO valorFormula, infoFormula;
pout_Formula     := valorformula;
pout_InfoFormula := infoFormula;
RETURN NEXT; --agrega los datos al conjunto resultado de salida
RETURN;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION pre_obtenervalorinfo(character varying, integer, character varying, character varying, character varying, character varying, character varying, 
                                    integer, character varying, character varying, character varying, character varying, 
                                             character varying, character varying, integer, character varying)
  OWNER TO abacouser;

-- Function: pre_obtenerprecioespecial(integer, integer, character varying, character varying, character varying, character varying, integer, character varying, character varying, character varying, character varying, character varying)

-- 
DROP FUNCTION IF EXISTS pre_obtenerprecioespecial(integer, integer, character varying, character varying, character varying,
             character varying, integer,
             character varying, character varying, character varying, character varying, character varying);

CREATE OR REPLACE FUNCTION pre_obtenerprecioespecial(
    integer,
    integer,
    character varying,
    character varying,
    character varying,
    character varying,
    integer,
    character varying,
    character varying,
    character varying,
    character varying,
    character varying,
    character varying)
  RETURNS character varying AS
$BODY$
DECLARE
id_PRE 		ALIAS FOR $1;
id_ART 		ALIAS FOR $2;
Ctxt_TIPOPREPAGA ALIAS FOR $3;
Ctxt_CODPREPAG   ALIAS FOR $4;
Ctxt_TIPOPADA    ALIAS FOR $5;
Ctxt_CODPADR     ALIAS FOR $6;
Ctxt_IDPAD       ALIAS FOR $7;
Ctxt_TIPOARTA    ALIAS FOR $8;
Ctxt_CODARTR     ALIAS FOR $9;
Ctxt_FECHAINI    ALIAS FOR $10;
Ctxt_FECHAFIN    ALIAS FOR $11;
Ctxt_CODSUC      ALIAS FOR $12;
Ctxt_TipoCOM     ALIAS FOR $13;

CampoPreEspFormula VARCHAR;
BEGIN
SELECT COALESCE(preesp.formula, '') INTO CampoPreEspFormula FROM preesp
LEFT JOIN padc ON ((COALESCE(preesp.tipopada, '') = padc.tipopada ) AND
(padc.codpadr || '.' LIKE COALESCE(preesp.codpadr, '') || '.%') AND
(padc.idpad = Ctxt_IDPAD))
LEFT JOIN artc ON ((COALESCE(preesp.tipoarta, '') = artc.tipoarta ) AND 
(artc.codartr || '.' LIKE COALESCE(preesp.codartr, '') || '.%') AND
(artc.idart = id_ART))
WHERE (preesp.idpre = id_PRE)
AND (
(
((COALESCE(preesp.tipoprepaga, '') = Ctxt_TIPOPREPAGA) OR (preesp.tipoprepaga IS NULL)) AND 
((COALESCE(preesp.codprepag, '') = Ctxt_CODPREPAG) OR (preesp.codprepag IS NULL))
) 
AND
(
(
((COALESCE(preesp.tipopada, '') = Ctxt_TIPOPADA) OR (preesp.tipopada IS NULL)) AND 
((Ctxt_CODPADR || '.' LIKE (COALESCE(preesp.codpadr, '') || '.%')) OR (preesp.codpadr IS NULL))
)
OR
(
(COALESCE(preesp.tipopada, '') = padc.tipopada ) AND
(padc.codpadr || '.' LIKE COALESCE(preesp.codpadr, '') || '.%')
)
)
AND
(
(COALESCE(preesp.idpad, -1) = Ctxt_IDPAD) OR (preesp.idpad IS NULL)
) 
AND
(
(
((COALESCE(preesp.tipoarta, '') = Ctxt_TIPOARTA) OR (preesp.tipoarta IS NULL)) AND 
((Ctxt_CODARTR || '.' LIKE (COALESCE(preesp.codartr, '') || '.%')) OR (preesp.codartr IS NULL))
) 
OR
(
(COALESCE(preesp.tipoarta, '') = artc.tipoarta ) AND 
(artc.codartr || '.' LIKE COALESCE(preesp.codartr, '') || '.%')
)
) 
AND
(
(preesp.idart = id_ART) OR (preesp.idart IS NULL)
)
AND
(
((COALESCE(CAST(CAST(preesp.fechavigenciaini AS DATE) AS VARCHAR), '') <= Ctxt_FECHAINI) OR (preesp.fechavigenciaini IS NULL)) AND 
((COALESCE(CAST(CAST(preesp.fechavigenciafin AS DATE) AS VARCHAR), '') >= Ctxt_FECHAFIN) OR (preesp.fechavigenciafin IS NULL))
) 
AND
(
(STRPOS(COALESCE(preesp.accesosucursales, ''), Ctxt_CODSUC) > 0) OR (preesp.accesosucursales IS NULL)
)
AND
(
((preesp.TiposCom IS NULL) OR position(';' || Ctxt_TipoCom || ';' IN preesp.TiposCom) > 0)
))
ORDER BY prioridad ASC LIMIT 1; --es el de menor prioridad
RETURN CampoPreEspFormula;	
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION pre_obtenerprecioespecial(integer, integer, character varying, character varying, character varying, character varying, integer, 
                character varying, character varying, character varying, character varying, character varying, character varying)
  OWNER TO abacouser;


-- Function: pre_obtenerprecioinfo(character varying, integer, character varying, character varying, character varying, integer)

-- 
DROP FUNCTION IF EXISTS pre_obtenerprecioinfo(character varying, integer, character varying, character varying, character varying, integer);

CREATE OR REPLACE FUNCTION pre_obtenerprecioinfo(
    character varying,
    integer,
    character varying,
    character varying,
    character varying,
    integer,
    character varying)
  RETURNS character AS
$BODY$
DECLARE
vble 		  ALIAS FOR $1;
id_ART 		  ALIAS FOR $2;
fechaEntrada	  ALIAS FOR $3;
codigoSuc	  ALIAS FOR $4;
codAmbTur	  ALIAS FOR $5;
NroCierre	  ALIAS FOR $6;
TipoCom		  ALIAS FOR $7;

Ctxt_TIPOPREPAGA VARCHAR;
Ctxt_CODPREPAG   VARCHAR;
Ctxt_TIPOPADA    VARCHAR;
Ctxt_CODPADR     VARCHAR;
Ctxt_IDPAD       INTEGER;
Ctxt_TIPOARTA    VARCHAR;
Ctxt_CODARTR     VARCHAR;
Ctxt_FECHAINI    VARCHAR;
Ctxt_FECHAFIN    VARCHAR;
Ctxt_CODSUC      VARCHAR;
valorNUMERAL_CANT VARCHAR;
precioArticulo	NUMERIC;
infoPrecioArticulo VARCHAR;
fechaConsulta	VARCHAR;
arbolArticulo	VARCHAR;
rubroArticulo	VARCHAR;
rec RECORD;
BEGIN
IF (COALESCE(fechaEntrada, '') = '') OR (fechaEntrada = '') THEN
fechaConsulta := CAST(CURRENT_DATE AS VARCHAR);
ELSE
fechaConsulta := fechaEntrada;
END IF;
SELECT INTO arbolArticulo, rubroArticulo
tipoArtA, codArtR
FROM art WHERE (id = id_ART);
--contexto vacio
Ctxt_TIPOPREPAGA := '';
Ctxt_CODPREPAG   := '';
Ctxt_TIPOPADA    := '';
Ctxt_CODPADR     := '';
Ctxt_IDPAD       := -1;
Ctxt_TIPOARTA    := arbolArticulo;
Ctxt_CODARTR     := rubroArticulo;
Ctxt_FECHAINI    := fechaConsulta;
Ctxt_FECHAFIN    := fechaConsulta;
Ctxt_CODSUC      := COALESCE(codigoSuc, '');
--valor para la vble de precio  especial  fija #CANT
valorNUMERAL_CANT := '0.0000';
SELECT * FROM pre_ObtenerPrecioArticuloInfo(vble, id_ART, fechaConsulta,
Ctxt_TIPOPREPAGA, Ctxt_CODPREPAG, 
Ctxt_TIPOPADA, Ctxt_CODPADR, Ctxt_IDPAD, Ctxt_TIPOARTA, Ctxt_CODARTR, 
Ctxt_FECHAINI, Ctxt_FECHAFIN, Ctxt_CODSUC, valorNUMERAL_CANT,
codambtur, nrocierre, TipoCom)
INTO precioArticulo, infoPrecioArticulo;                                    
RETURN CAST(CAST(precioArticulo AS NUMERIC(16,4)) AS VARCHAR) || ';' || COALESCE(CAST(InfoPrecioArticulo AS VARCHAR),'');
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION pre_obtenerprecioinfo(character varying, integer, character varying, character varying, character varying, integer, character varying)
  OWNER TO abacouser;
COMMENT ON FUNCTION pre_obtenerprecioinfo(character varying, integer, character varying, character varying, character varying, integer, character varying)
 IS 'Funcion llamada desde la app. (ART_QryArticulos_Obj, PRE__Data)';

-- ---------------------------------
SELECT aaVersion('443.00','442.02');
-- ---------------------------------
